﻿CREATE PROCEDURE [dbo].[CustomerCommunicationSummaryReport]
	@BeginDate datetime, 
	@EndDate datetime,
	@ProjectId int
WITH RECOMPILE 
AS
BEGIN
	SET @EndDate = DateAdd(day, 1, @EndDate);
	WITH 
	[ProjectView]
	AS
	(
		SELECT	[Id], 
						[Name]
		FROM		[dbo].[Project]
		WHERE	[IsDeleted] = 0 AND [Id] = @ProjectId
	),
	[CustomerRequirementView]
	AS
	(
		SELECT	[Id],
						[CustomerId], 
						[RequirementLevelId], 
						[MotivationId]
		FROM		[dbo].[CustomerRequirement]
		WHERE	[IsDeleted] = 0
	),
	[CustomerView]
	AS
	(
		SELECT	C.[Id],
						C.[ProjectId],
						CR.[RequirementLevelId], 
						CR.[MotivationId]
		FROM		[dbo].[Customer] AS C
			INNER JOIN [CustomerRequirementView] AS CR ON C.[Id] = CR.[CustomerId] 
		WHERE	C.[IsDeleted] = 0
	),
	[CustomerCommunicationView]
	AS
	(
		SELECT	[Id],
						[CustomerId], 
						[CommunicationTypeId]
		FROM		[dbo].[CustomerCommunication]
	),
	[AllView]
	AS
	(
		SELECT	C.[RequirementLevelId], 
						C.[MotivationId],
						CC.[CommunicationTypeId]
		FROM		[dbo].[CustomerCommunication] AS CC
			INNER JOIN [CustomerView] AS C ON CC.[CustomerId] = C.[Id]
			INNER JOIN [ProjectView] AS P ON C.[ProjectId] = P.[Id]
		WHERE	CC.[IsDeleted] = 0 AND CC.[CommunicationDate] >= @BeginDate AND CC.[CommunicationDate] < @EndDate
	)
	SELECT	(SELECT COUNT(*) FROM [AllView] WHERE [CommunicationTypeId] = 1 AND [RequirementLevelId] = 1) AS [Type1Level1Count], 
					(SELECT COUNT(*) FROM [AllView] WHERE [CommunicationTypeId] = 1 AND [RequirementLevelId] = 2) AS [Type1Level2Count], 
					(SELECT COUNT(*) FROM [AllView] WHERE [CommunicationTypeId] = 1 AND [RequirementLevelId] = 3) AS [Type1Level3Count], 
					(SELECT COUNT(*) FROM [AllView] WHERE [CommunicationTypeId] = 1) AS [Type1Count], 
					CASE WHEN (SELECT COUNT(*) FROM [AllView] WHERE [CommunicationTypeId] = 1) = 0 THEN 0 ELSE (SELECT CAST(COUNT(*) AS numeric(20, 2)) FROM [AllView] WHERE [CommunicationTypeId] = 1 AND [MotivationId] = 1) / (SELECT CAST(COUNT(*) AS numeric(20, 2)) FROM [AllView] WHERE [CommunicationTypeId] = 1) END AS [Type1Motivation1Ratio], 
					CASE WHEN (SELECT COUNT(*) FROM [AllView] WHERE [CommunicationTypeId] = 1) = 0 THEN 0 ELSE (SELECT CAST(COUNT(*) AS numeric(20, 2)) FROM [AllView] WHERE [CommunicationTypeId] = 1 AND [MotivationId] = 2) / (SELECT CAST(COUNT(*) AS numeric(20, 2)) FROM [AllView] WHERE [CommunicationTypeId] = 1) END AS [Type1Motivation2Ratio], 
					CASE WHEN (SELECT COUNT(*) FROM [AllView] WHERE [CommunicationTypeId] = 1) = 0 THEN 0 ELSE (SELECT CAST(COUNT(*) AS numeric(20, 2)) FROM [AllView] WHERE [CommunicationTypeId] = 1 AND [MotivationId] = 3) / (SELECT CAST(COUNT(*) AS numeric(20, 2)) FROM [AllView] WHERE [CommunicationTypeId] = 1) END AS [Type1Motivation3Ratio], 
					CASE WHEN (SELECT COUNT(*) FROM [AllView] WHERE [CommunicationTypeId] = 1) = 0 THEN 0 ELSE (SELECT CAST(COUNT(*) AS numeric(20, 2)) FROM [AllView] WHERE [CommunicationTypeId] = 1 AND [MotivationId] > 3) / (SELECT CAST(COUNT(*) AS numeric(20, 2)) FROM [AllView] WHERE [CommunicationTypeId] = 1) END AS [Type1MotivationOtherRatio], 

					(SELECT COUNT(*) FROM [AllView] WHERE [CommunicationTypeId] = 2 AND [RequirementLevelId] = 1) AS [Type2Level1Count], 
					(SELECT COUNT(*) FROM [AllView] WHERE [CommunicationTypeId] = 2 AND [RequirementLevelId] = 2) AS [Type2Level2Count], 
					(SELECT COUNT(*) FROM [AllView] WHERE [CommunicationTypeId] = 2 AND [RequirementLevelId] = 3) AS [Type2Level3Count], 
					(SELECT COUNT(*) FROM [AllView] WHERE [CommunicationTypeId] = 2) AS [Type2Count], 
					CASE WHEN (SELECT COUNT(*) FROM [AllView] WHERE [CommunicationTypeId] = 2) = 0 THEN 0 ELSE (SELECT CAST(COUNT(*) AS numeric(20, 2)) FROM [AllView] WHERE [CommunicationTypeId] = 2 AND [MotivationId] = 1) / (SELECT CAST(COUNT(*) AS numeric(20, 2)) FROM [AllView] WHERE [CommunicationTypeId] = 2) END AS [Type2Motivation1Ratio], 
					CASE WHEN (SELECT COUNT(*) FROM [AllView] WHERE [CommunicationTypeId] = 2) = 0 THEN 0 ELSE (SELECT CAST(COUNT(*) AS numeric(20, 2)) FROM [AllView] WHERE [CommunicationTypeId] = 2 AND [MotivationId] = 2) / (SELECT CAST(COUNT(*) AS numeric(20, 2)) FROM [AllView] WHERE [CommunicationTypeId] = 2) END AS [Type2Motivation2Ratio], 
					CASE WHEN (SELECT COUNT(*) FROM [AllView] WHERE [CommunicationTypeId] = 2) = 0 THEN 0 ELSE (SELECT CAST(COUNT(*) AS numeric(20, 2)) FROM [AllView] WHERE [CommunicationTypeId] = 2 AND [MotivationId] = 3) / (SELECT CAST(COUNT(*) AS numeric(20, 2)) FROM [AllView] WHERE [CommunicationTypeId] = 2) END AS [Type2Motivation3Ratio], 
					CASE WHEN (SELECT COUNT(*) FROM [AllView] WHERE [CommunicationTypeId] = 2) = 0 THEN 0 ELSE (SELECT CAST(COUNT(*) AS numeric(20, 2)) FROM [AllView] WHERE [CommunicationTypeId] = 2 AND [MotivationId] > 3) / (SELECT CAST(COUNT(*) AS numeric(20, 2)) FROM [AllView] WHERE [CommunicationTypeId] = 2) END AS [Type2MotivationOtherRatio], 

					(SELECT COUNT(*) FROM [AllView] WHERE [CommunicationTypeId] = 3 AND [RequirementLevelId] = 1) AS [Type3Level1Count], 
					(SELECT COUNT(*) FROM [AllView] WHERE [CommunicationTypeId] = 3 AND [RequirementLevelId] = 2) AS [Type3Level2Count], 
					(SELECT COUNT(*) FROM [AllView] WHERE [CommunicationTypeId] = 3 AND [RequirementLevelId] = 3) AS [Type3Level3Count], 
					(SELECT COUNT(*) FROM [AllView] WHERE [CommunicationTypeId] = 3) AS [Type3Count], 
					CASE WHEN (SELECT COUNT(*) FROM [AllView] WHERE [CommunicationTypeId] = 3) = 0 THEN 0 ELSE (SELECT CAST(COUNT(*) AS numeric(20, 2)) FROM [AllView] WHERE [CommunicationTypeId] = 3 AND [MotivationId] = 1) / (SELECT CAST(COUNT(*) AS numeric(20, 2)) FROM [AllView] WHERE [CommunicationTypeId] = 3) END AS [Type3Motivation1Ratio], 
					CASE WHEN (SELECT COUNT(*) FROM [AllView] WHERE [CommunicationTypeId] = 3) = 0 THEN 0 ELSE (SELECT CAST(COUNT(*) AS numeric(20, 2)) FROM [AllView] WHERE [CommunicationTypeId] = 3 AND [MotivationId] = 2) / (SELECT CAST(COUNT(*) AS numeric(20, 2)) FROM [AllView] WHERE [CommunicationTypeId] = 3) END AS [Type3Motivation2Ratio], 
					CASE WHEN (SELECT COUNT(*) FROM [AllView] WHERE [CommunicationTypeId] = 3) = 0 THEN 0 ELSE (SELECT CAST(COUNT(*) AS numeric(20, 2)) FROM [AllView] WHERE [CommunicationTypeId] = 3 AND [MotivationId] = 3) / (SELECT CAST(COUNT(*) AS numeric(20, 2)) FROM [AllView] WHERE [CommunicationTypeId] = 3) END AS [Type3Motivation3Ratio], 
					CASE WHEN (SELECT COUNT(*) FROM [AllView] WHERE [CommunicationTypeId] = 3) = 0 THEN 0 ELSE (SELECT CAST(COUNT(*) AS numeric(20, 2)) FROM [AllView] WHERE [CommunicationTypeId] = 3 AND [MotivationId] > 3) / (SELECT CAST(COUNT(*) AS numeric(20, 2)) FROM [AllView] WHERE [CommunicationTypeId] = 3) END AS [Type3MotivationOtherRatio], 

					(SELECT COUNT(*) FROM [AllView] WHERE [CommunicationTypeId] = 4 AND [RequirementLevelId] = 1) AS [Type4Level1Count], 
					(SELECT COUNT(*) FROM [AllView] WHERE [CommunicationTypeId] = 4 AND [RequirementLevelId] = 2) AS [Type4Level2Count], 
					(SELECT COUNT(*) FROM [AllView] WHERE [CommunicationTypeId] = 4 AND [RequirementLevelId] = 3) AS [Type4Level3Count], 
					(SELECT COUNT(*) FROM [AllView] WHERE [CommunicationTypeId] = 4) AS [Type4Count], 
					CASE WHEN (SELECT COUNT(*) FROM [AllView] WHERE [CommunicationTypeId] = 4) = 0 THEN 0 ELSE (SELECT CAST(COUNT(*) AS numeric(20, 2)) FROM [AllView] WHERE [CommunicationTypeId] = 4 AND [MotivationId] = 1) / (SELECT CAST(COUNT(*) AS numeric(20, 2)) FROM [AllView] WHERE [CommunicationTypeId] = 4) END AS [Type4Motivation1Ratio], 
					CASE WHEN (SELECT COUNT(*) FROM [AllView] WHERE [CommunicationTypeId] = 4) = 0 THEN 0 ELSE (SELECT CAST(COUNT(*) AS numeric(20, 2)) FROM [AllView] WHERE [CommunicationTypeId] = 4 AND [MotivationId] = 2) / (SELECT CAST(COUNT(*) AS numeric(20, 2)) FROM [AllView] WHERE [CommunicationTypeId] = 4) END AS [Type4Motivation2Ratio], 
					CASE WHEN (SELECT COUNT(*) FROM [AllView] WHERE [CommunicationTypeId] = 4) = 0 THEN 0 ELSE (SELECT CAST(COUNT(*) AS numeric(20, 2)) FROM [AllView] WHERE [CommunicationTypeId] = 4 AND [MotivationId] = 3) / (SELECT CAST(COUNT(*) AS numeric(20, 2)) FROM [AllView] WHERE [CommunicationTypeId] = 4) END AS [Type4Motivation3Ratio], 
					CASE WHEN (SELECT COUNT(*) FROM [AllView] WHERE [CommunicationTypeId] = 4) = 0 THEN 0 ELSE (SELECT CAST(COUNT(*) AS numeric(20, 2)) FROM [AllView] WHERE [CommunicationTypeId] = 4 AND [MotivationId] > 3) / (SELECT CAST(COUNT(*) AS numeric(20, 2)) FROM [AllView] WHERE [CommunicationTypeId] = 4) END AS [Type4MotivationOtherRatio]
END